﻿using System;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using System.Data;
using System.IO;
using ConsolidationTool.Common;

namespace ConsolidationTool.WebParts.Layouts.ConsolidationTool.WebParts
{
    public partial class Budget_LoadingFile : LayoutsPageBase
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            Button1.Click += new EventHandler(Button1_Click);
        }
        DataTable gvLFResult = new DataTable();

        void Button1_Click(object sender, EventArgs e)
        {
            Stream stream = FileUpload1.FileContent;
            DataTable dt = Utilities.GetSheets(stream);
            //dt.Columns.Count
            gvLFResult.Columns.Add("fs_Index");
            gvLFResult.Columns.Add("fs_Status");
            for (int i = 0; i < 4; i++)
                gvLFResult.Columns.Add(dt.Columns[i].ColumnName);

            gvLFResult.Columns.Add("fs_OtherField");

            SPWeb web = SPContext.Current.Web;
            SPList loadingFileList = MergaLoadFileListStructureByDataTable("Budget_RGC_LoadingFile", dt, web);

            gvLoadingFileTable.DataSource = gvLFResult;
            gvLoadingFileTable.DataBind();
        }

        private SPList MergaLoadFileListStructureByDataTable(string listname, DataTable dt, SPWeb web)
        {
            SPList loadingFileList = web.Lists.TryGetList(listname);
            if (loadingFileList == null)
            {
                lbOutput.Text += listname + " is not exist creating now ...<br/>";
                Guid listID = web.Lists.Add(listname, listname, SPListTemplateType.GenericList);
                loadingFileList = web.Lists[listID];
                loadingFileList.Fields.Add("Year", SPFieldType.Text, false);

              //  lbOutput.Text += listname + " created ...<br/>";
            }
            // create list structure
            foreach (DataColumn column in dt.Columns)
            {
                try
                {
                    string fieldName = column.ColumnName.Replace(" ", "").Replace("&", "").Replace("<", "").Replace(">", "").Replace("/", "").Replace("\\", "").Replace("(", "").Replace(")", "").Replace(".", "").Replace(",", "").Replace(";", "").Replace(":", "").Replace("{", "").Replace("}", "").Replace("-", "").Replace("+", "").Replace("\"", "").Replace("`\"", "");
                    SPField field = loadingFileList.Fields.TryGetFieldByStaticName(fieldName);
                    if (field == null)
                    {
                        //   lbOutput.Text += column.ColumnName + " is not exist creating now ...<br/>";

                        /* create a Numeric field for EmpID */
                        SPFieldText newField = (SPFieldText)loadingFileList.Fields.CreateNewField(
                                        SPFieldType.Text.ToString(), column.ColumnName);
                        newField.Required = false;
                        newField.StaticName = fieldName;

                        loadingFileList.Fields.Add(newField);

                      //  lbOutput.Text += fieldName + " created ...<br/>";
                    }

                }
                catch (Exception ex)
                {
                    lbOutput.Text += ex.Message + "<br/>";

                }
            }
            // add row data
            int index = 1;
            foreach (DataRow row in dt.Rows)
            {
                SPListItem newItem = loadingFileList.Items.Add();
                DataRow newRow = gvLFResult.NewRow();
                try
                {

                    foreach (DataColumn column in dt.Columns)
                    {
                        newItem[column.ColumnName] = row[column];

                        if (gvLFResult.Columns.Contains(column.ColumnName))
                        {
                            newRow[column.ColumnName] = string.IsNullOrEmpty(row[column] + "") ? 0 : row[column];
                        }
                        //else
                        //{
                        //    newRow["fs_OtherField"] = newRow["fs_OtherField"] + ",\"" + column.ColumnName + "\":\"" + row[column] + "\"";
                        //}
                    }
                    newItem["Title"] = row["Year"] + "-" + row["Company"] + "-" + row["Period"];
                    newItem.Web.AllowUnsafeUpdates = true;
                    newItem.Update();
                    newItem.Web.AllowUnsafeUpdates = false;
                    newRow["fs_Status"] = "Success";
                    newRow["fs_Index"] = index++;

                }
                catch (Exception ex)
                {
                    newRow["fs_OtherField"] = newRow["fs_OtherField"] + "[Error]-----" + ex.Message;
                    newRow["fs_Status"] = "Fail";
                }
                gvLFResult.Rows.Add(newRow);
            }

            return loadingFileList;
        }
    }
}
